How-to: Updating SQL Server 2017 and 2019
Introduction
Before installing the SQL server, check the system
requirements for the specific SQL server edition at https://msdn.microsoft.com/en-us/library/ms143506(v=sql.140).aspx.
Ensure the existing SQL server supports updates. For a list
of supported upgrade scenarios, see https://msdn.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2017.
Note: Back up all your databases.
Upgrading the SQL server
- Log in
as an administrator on the server (of the workstation for a standalone
installation).
- Insert
the SQL server installation DVD into the DVD drive. If the autorun does not execute,
open the DVD drive in Windows Explorer and start the installation by clicking Setup.ext.
The following screen will be displayed:
- Click
Installation on the left panel, and then click Upgrade from a
previous version of SQL Server on the right panel. The following screen
will be displayed:
- Select
the Enter the product key option and type the product key if the product
key is not filled automatically. Click Next to continue. The following
screen will be displayed:
- Select
the I accept the license terms check box, and then click Next.
The following screen will be displayed:
- Checks
will be performed to identify the possible problems when installing the SQL
Server Setup support files. The SQL Server Setup support files are needed for
the installation of SQL Server 2017 and 2019, and are automatically installed via this
installation wizard. Click Show details >> to view the details of the
checks. If all the checks are passed, the results will be displayed in the Global
Rules screen. However, if a check has failed, a red icon will be displayed.
You can click the hyperlink in the Status column to view the details of
the check and error(s). Once the error is fixed, click Re-run to perform
the checks again.
Note:
Some warnings about the firewall might be displayed. Despite the warnings, you
can still continue with the installation. Depending on the situation, it is not
necessary to close the installation wizard and restart. If all the checks are passed,
the Global Rules screen will close automatically and the following
screen will be displayed:
- The Microsoft
Update screen allows you to check for new update files online. If you
select the Use Microsoft Update to check for updates (recommended) check
box, the Product Updates screen will be displayed whereby you can
download the files. Otherwise, the Install Setup Files screen will be
displayed.
- The
installation wizard will check for updates and install the available update
files. Once the files are updated, the screen will close automatically and the
next screen will be displayed:
- In
the Upgrade Rules screen, checks will be performed to identify the
problems that might occur during the installation. Click Show details
>> to view the details of the checks. If a check has failed, a red
icon will be displayed. You can click the hyperlink in the Status column
for more information on the check and error(s). Once the issue is solved, click
Re-run to perform the checks again.
Note:
Depending on the situation, it is not necessary to close the installation
wizard and restart.
- Click Next and the
following screen will be displayed:
- At Select Instance,
select the instance of the SQL server that you want to upgrade. The instance(s)
will be displayed under the Installed instances section. The Instance
Name column displays the name of the instance. The Version column
displays the version of the instance. Version numbers that start with 10 apply
to SQL Server 2008 (R2), version numbers that start with 11 apply to SQL Server
2012, and version numbers that start with 12 apply to SQL Server 2014, and
version numbers that start with 13 apply to SQL Server 2016. The <Shared
Components> line applies to the extra components of the SQL server that
are installed, such as SQL Server Management Studio. Click Next and the
following screen will be displayed:
- The Reporting
Services Migration screen is displayed only if the reporting services
component is installed for the previous SQL server version.
Note: If
Reporting Services is installed, this will be removed during the upgrade.
Before you continue, back up your report server database, and your encryption
key. Furthermore, Reporting Services for SQL Server 2017 and 2019 is done via a separate
installation, and you have to migrate the reports. For more information, see How-to:
Installing SQL Server Reporting Services (SSRS).
Once you have made a backup of the
report server database, and the encryption key, you can select the Uninstall
Reporting Service check box, and click Next to continue.
- The Select
Features screen displays the components that will be upgraded. By default,
the components that will be upgraded will be selected. You cannot change
anything in the screen. This screen will close automatically.
- In the Instance
Configuration screen, you can define the name and instance ID of the SQL server
instance. Since you are upgrading the SQL server, you will not be able to
change the name of the SQL instance. The SQL instance will use the same name.
You do not have to change the instance ID and you can accept the value
displayed in the screen. Click Next and the following screen will be
displayed:
- Under the Service
Accounts tab in the Server Configuration screen, you can define the
account that you want to use to start the SQL Server 2017 or 2019 services. You do not
have to change anything. Click Next and the following screen will be
displayed:
- The Full-text
Upgrade screen is displayed only if the Full-Text and semantic
extractions for Search component of SQL server is installed. This screen
allows you to select an upgrade option for this component. This component is
applicable if you use the SQL full text search to search for feeds in the
timeline in Exact Synergy Enterprise. For more information, see Defining
social collaboration settings. For this Exact Synergy Enterprise
functionality, it is not important which option is selected. If you are using
the SQL full text search component for other purposes, check with your system
administrator to determine which option to select. Click Next to
continue.
- In the Feature
Rules screen, checks will be performed to identify the problems that might
occur during the installation. Click Show details >> to view the
details of the checks. When a check has failed, a red icon will be displayed.
You can click the hyperlink in the Status column for more information on
the check and error(s). After you have fixed the issue, click Re-run to
perform the checks again. The screen will automatically close after all the
checks are successful.
Note:
Depending on the situation, it is not necessary to close the installation
wizard and restart.
- In the Ready to
Upgrade screen, the list of upgrade and installation criteria will be
displayed. Click Upgrade to start the upgrade and installation.
- The Upgrade
Progress screen displays the progress of the installation.
- Once the installation
has completed successfully, the installed components will be displayed with a
green icon. Click Close to exit.
- To
be able to access the SQL server via a network, you have to activate some
protocols. When working with a network environment, you have to check if the
protocols are activated. If required, activate the protocols.
- Start (on the server where MS SQL
Server 2017 or 2019 is installed) SQL Server Configuration Manager. In
Windows, go to Start, select All Programs, followed by Microsoft
SQL Server 2017. Click Configuration Tools, and then SQL
Server 2017 Configuration Manager.
In Windows 8, press the Windows key and the Q key on your keyboard. On
the right at Search at the Apps field, type “SQL Server
Configuration Manager”. Next, click SQL Server 2017 Configuration
Manager.
- Click SQL Server Network
Configuration.
- Select Protocols for xxxx. (“xxxx”
has to be replaced by the name you have defined for the SQL server during
installation.)
- Right-click the protocols Named
Pipes and TCP/IP and click Enable to enable these
protocols.
- You might also need to start the SQL
Server Browser service. This service is used for identifying the ports
that the installation of SQL server listens to. When this service is not
started, you can use the SQL server only locally. To check if this service
is started, follow these steps:
- Start (on the server where MS SQL
Server 2017 or 2019 is installed) SQL Server Configuration Manager. In Windows,
go to Start, select All Programs, followed by SQL Server 2017.
Click Configuration Tools, and then SQL 2017 Server Configuration
Manager.
In Windows 8, press the Windows key and the Q key on your keyboard. On the
right at Search at the Apps field, type “SQL Server Configuration
Manager”. Next, click SQL Server 2017 Configuration Manager on the left
side of the screen.
- Click SQL Server Services.
- On the right side, the SQL Server
Browser service is displayed.
Note: When it is not started, the icon is red. When it is started, the
icon is green. Ensure that the service starts automatically so that every time
the system is restarted, the service will also start.
- Right-click SQL Server Browser
and select Properties.
- In the SQL Server Browser Properties
screen, click the Service tab.
- At Start Mode, select Automatic,
as displayed in the following screen:
- Click Apply to apply the
changes. The SQL Server Browser service will now start automatically
every time the system is started.
- Next, start SQL Server Browser.
- In the SQL Server Browser Properties
screen, click the Log On tab.
- Click Start to start the service. When the Start
button is inactive, the SQL Server Browser service is already started
and you do not have to start it.
Note: When you have a firewall, you have to configure the firewall to
allow connections with SQL Server 2017 or 2019. The default port for SQL Server 2017 and 2019 is
1433. The SQL browser used UDP port 1434.
Installing SQL Server Management Studio
The next step is to install SQL Server Management Studio. SQL
Server Management Studio is an application for managing the SQL Server. With
SQL Server Management Studio, you can create and restore backups, change
database settings, and give users SQL server rights.
Note: If you already have an older version of SQL
Server Management Studio installed, it is advisable to install the most recent
version of SQL Server Management Studio. The new version will be installed
side-by-side with the previous versions so both the versions can be used.
- Log in as an administrator on the system where you have installed
SQL Server 2017 or 2019.
- The setup file for SQL Server Management Studio can be downloaded
from the Microsoft website (https://msdn.microsoft.com/library/mt238290.aspx).
Place this file on the system where MS SQL Server 2017 or 2019 is installed.
- Start the installation by double-clicking the downloaded file.
The following screen will be displayed:
Click Install to start the installation.
During the installation, you can follow the progress of the
installation. The following screens will be displayed. The installation can
take some time.
When the installation is completed, the following screen
will be displayed. You have to restart the system to complete the setup.
Preparing your database to use SQL Server 2017 or 2019
Change the compatibility level of your upgraded databases
- Go
to Start ➔ All Programs ➔ Microsoft SQL Server 2017/2019 ➔
Microsoft SQL Server Management Studio.
- Connect
to the SQL server.
- Expand
the tree view until you see the databases.
- Right-click
a company database and select Properties.
- In
the Database Properties screen, select Options on the left panel
and select SQL Server 2017 (140) at Compatibility level on the
right panel, and then click OK.
Note: You have to change the Compatibility level
for every company database.
Updating indexes and statistics
For optimal performance, it is important to update the
indexes and statistics. To create these optimization jobs, you can use the ESI
tool. For more information, see Exact System Information (ESI) help file.
Related documents
Main Category: |
Attachments & notes |
Document Type: |
Online help main |
Category: |
|
Security level: |
All - 0 |
Sub category: |
|
Document ID: |
28.428.251 |
Assortment: |
Exact Synergy Enterprise
|
Date: |
01-04-2021 |
Release: |
|
Attachment: |
|
Disclaimer |